package mysqlCnnection;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

/**
 * This class creates/updates template and dealsale tables in DB
 * @author ameer
 * @author waleed
 * @author bshara
 * @author kamil
 */
public class TemplateController extends ConnectionTOmysql{
	private Connection connection;
	
	/**
	 * Default constructor
	 * @throws IlegalMySQLPasswordORUserNameException 
	 */
	public TemplateController(){
		super();
		this.connection = super.getConnection();
	}
	
	/**
	 * This function return all clients market sectors as String[] 
	 * @return String[] 
	 */
	public String[] loadAllClientsMarketSectors(){
		Statement stmt;
		String[] marketsectorsID = null;
		int len=1, i=0;
		Vector<Integer> vecMarketsectorsID = new Vector<Integer>();
		try{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT sNumber FROM marketsectors;");
			while(rs.next()){
				vecMarketsectorsID.add(rs.getInt(1));
			}	
			len = vecMarketsectorsID.size();
			marketsectorsID = new String[len];
			for (i=0; i < len; i++)
				marketsectorsID[i] = String.valueOf(vecMarketsectorsID.get(i));			
			rs.close();
			return marketsectorsID;
		} 
		catch (SQLException e){
			return null;
		}
		catch (Exception exc){
			return null;
		}		
	}
	
	/**
	 * This function creates a new template to DB
	 * @param tID the template id
	 * @param name template name
	 * @param sector the clients sector of the template
	 * @param message the message to customer
	 * @param pID the product id of the template
	 * @return  "OK" on success , "IlegalID" on fail
	 */
	public String createTemplate(long tempID, String tempName, String sector, String msg, long prodID ){
		try {
			PreparedStatement ps = connection.prepareStatement("INSERT INTO campaigns VALUES (?,?,?,?,?);");
			ps.setLong(1, tempID);
			ps.setString(2, tempName);
			ps.setString(3, msg);
			ps.setLong(4, Long.parseLong(sector));		
			ps.setLong(5, prodID);		
			ps.executeUpdate();
			ps.close();
			return "OK";
		}
		catch (SQLException e){
		 	return "IlegalID";
		}
		catch (Exception e){
		 	return null;
		}
	}
	
	/**
	 * This function imports all templates from DB
	 * @return array of String of all Template IDs
	 */
	public String[] loadAllTemplates(){
		Statement stmt;
		String[] template = null;
		int len=1, i=0;
		Vector<String> Template = new Vector<String>();
		try{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM campaigns;");
			while(rs.next()){
				Template.add(rs.getString(1)+": "+rs.getString(2));
			}	
			len = Template.size();
			template = new String[len];
			for (i=0; i < len; i++)
				template[i] = Template.get(i);			
			rs.close();
			return template;
		} 
		catch (SQLException e){
			return null;
		}
		catch (Exception exc){
			return null;
		}		
	}
	
	/**
	 * This function imports template details from DB
	 * @param tempID the template id
	 * @return array of string of template details
	 */
	public String[] loadTemplate(long tempID){
		Statement stmt;
		String[] templates = null;
		long prodID = 0;
		try{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM campaigns WHERE campaignID = "+tempID+";");
			templates = new String[8];
			if (rs.next()){	
				templates[0] = String.valueOf(rs.getLong(1)); 
				templates[1] = rs.getString(2); 
				templates[2] = rs.getString(3);
				templates[3] = String.valueOf(rs.getInt(4));
				prodID = rs.getLong(5);
				rs.close();	
				ResultSet Result = stmt.executeQuery("SELECT * FROM catalogue WHERE cataloguenumber = "+prodID+";");
				if (Result.next()){
					templates[4] = Result.getString(1);
					templates[5] = Result.getString(2);
					templates[6] = Result.getString(3);
					templates[7] = Result.getString(4);
					return templates;
				}									
			}
			return null;			
		} 
		catch (SQLException e){
			return null;
		}
	}
	
	/**
	 * This function update template details in DB
	 * @param tempID the template id
	 * @param name the name of the campaign
	 * @param sector the clients sector of the campaign
	 * @param message the message to customer
	 * @param pID the id of the product of the campaign
	 * @return "OK" on success , "ERROR" on SQL error
	 */
	public String updateTemplate(long tempID, String tempName, String sector, String message, long prodID){
		String statementstr = null;
		Statement stmt;
		long tmpsector = Long.parseLong(sector);
		statementstr = String.format("UPDATE campaigns SET campaignName='%s', " +
				"message='%s',sectorNumber=%d, productNumber=%d WHERE campaignID=%d;",
				tempName,message,tmpsector,prodID,tempID);
		try {
			stmt = connection.createStatement();
			stmt.executeUpdate(statementstr);
			stmt.close();
			return "OK";
		} 
		catch (SQLException e){
			return "ERROR";
		}	
	}
	
	/**
	 * This function updates deal sale details in DB
	 * @param dealsaleID the deal sale id
	 * @param startdate the deal sale start date
	 * @param endate the deal sale end date
	 * @param status the deal sale status
	 * @return "OK" on success , "ERROR" on fail
	 */
	public String updateDealSale(long dealsaleID, Date startdate, Date endate, String status){
		try{
			PreparedStatement ps = connection.prepareStatement("UPDATE activedcampaign SET startdate=?, enddate=?, status=? WHERE ActiveID=?;");
			ps.setDate(1, startdate);
			ps.setDate(2, endate);
			ps.setString(3, status);
			ps.setLong(4, dealsaleID);			
			ps.executeUpdate();
			ps.close();
			return "OK";
		} 
		catch (SQLException e){
			return "ERROR";
		}	
	}
	
	/**
	 * This function adds a new deal sale to DB
	 * @param dealsaleID the deal sale id
	 * @param startdate the deal sale start date
	 * @param endate the deal sale end date
	 * @param status the deal sale status
	 * @param tempID the template id
	 * @return "OK" on success , "ERROR" on fail
	 */
	public String saveDealSale(long dealsaleID, Date startdate, Date endate, String status , long tempID){
		try{
			PreparedStatement ps = connection.prepareStatement("INSERT INTO activedcampaign VALUES (?,?,?,?,?);");
			ps.setLong(1, dealsaleID);	
			ps.setLong(2, tempID);	
			ps.setDate(3, startdate);
			ps.setDate(4, endate);
			ps.setString(5, status);				
			ps.executeUpdate();
			ps.close();
			return "OK";
		} 
		catch (SQLException e){
			return "ERROR";
		}	
	}
	
	/**
	 * This function imports deal sale details from DB
	 * @param dealsaleID the deal sale id
	 * @return Object[] on success, null on fail
	 */
	public Object[] loadActiveCampaign(long dealsaleID){
		Statement stmt;
		Object[] dealSales = null;
		try{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM activedcampaign WHERE ActiveID = "+dealsaleID+";");
			dealSales = new Object[5];
			if (rs.next()){	
				dealSales[0] = rs.getLong(1);
				dealSales[4] = rs.getLong(2);
				dealSales[1] = rs.getDate(3);
				dealSales[2] = rs.getDate(4);
				dealSales[3] = rs.getString(5);
				rs.close();
				return dealSales;
			}
			return null;			
		} 
		catch (SQLException e){
			return null;
		}		
	}
	
	/**
	 * This function imports all deal sales from DB
	 * @return Vector<Long> on success, null on fail
	 */
	public long[] loadAllDealSales(){
		Statement stmt;
		long[] tempIDs = null;
		int len=1, i=0;
		Vector<Long> dealsaleIDs = new Vector<Long>();
		try{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT ActiveID FROM activedcampaign;");
			while(rs.next()){
				dealsaleIDs.add(rs.getLong(1));
			}	
			len = dealsaleIDs.size();
			tempIDs = new long[len];
			for (i=0; i < len; i++)
				tempIDs[i] = dealsaleIDs.get(i);			
			rs.close();
			return tempIDs;
		} 
		catch (SQLException e){
			return null;
		}
		catch (Exception exc){
			return null;
		}		
	}
}
	
	

